package dao.Impl;

import dao.StudentDao;
import db.DBCon;
import model.PageBean;
import model.Student;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {

    private DBCon dbCon = new DBCon();
    private Student student;
    private PageBean pageBean;

    public int addStu(Student student) {
        String sql = "insert into student(stu_num,stu_name,stu_sex,stu_age,stu_class,major,department) values('"
                + student.getStuNum()
                + "','"
                + student.getStuName()
                + "','"
                + student.getStuSex()
                + "',"
                + student.getStuAge()
                + ",'"
                + student.getStuClass()
                + "','"
                + student.getMajor()
                + "','"
                + student.getDepartment() + "')";
        int rs = dbCon.query(sql);

        return rs;
    }

    public int deleteStu(String stuNum) {
        String sql = "delete from student where stu_num='" + stuNum + "'";
        int rs = dbCon.query(sql);
        return rs;
    }

    public int editStu(Student student) {
        String sql = "update student set stu_name='" + student.getStuName()
                + "',stu_sex='" + student.getStuSex() + "',stu_age="
                + student.getStuAge() + ",stu_class='" + student.getStuClass()
                + "',major='" + student.getMajor() + "',department='"
                + student.getDepartment() + "' where stu_num='"
                + student.getStuNum() + "'";
        int rs = dbCon.query(sql);
        return rs;
    }

    public List findAll() {
        List<Student> list = new ArrayList<Student>();
        String sql = "select * from student";
        ResultSet rs = dbCon.find(sql);
        try {
            while (rs.next()) {
                int stuID = rs.getInt("stu_id");
                String stuNum = rs.getString("stu_num");
                String stuName = rs.getString("stu_name");
                String stuSex = rs.getString("stu_sex");
                int stuAge = rs.getInt("stu_age");
                String stuClass = rs.getString("stu_class");
                String major = rs.getString("major");
                String department = rs.getString("department");
                student = new Student(stuID, stuNum, stuName, stuSex, stuAge,
                        stuClass, major, department);
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List findOne(String stuNum) {
        List<Student> list = new ArrayList<Student>();
        String sql = "select * from student where stu_num='" + stuNum + "'";
        ResultSet rs = dbCon.find(sql);
        try {
            while (rs.next()) {
                int stuID = rs.getInt("stu_id");
                String stunum = rs.getString("stu_num");
                String stuName = rs.getString("stu_name");
                String stuSex = rs.getString("stu_sex");
                int stuAge = rs.getInt("stu_age");
                String stuClass = rs.getString("stu_class");
                String major = rs.getString("major");
                String department = rs.getString("department");
                student = new Student(stuID, stunum, stuName, stuSex, stuAge,
                        stuClass, major, department);
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    //pageNo当前页码，pageCount每页显示总条数
    public PageBean stuListPage(int pageNo, int pageCount) {
        int totalCount=0;
        List<Student> list = new ArrayList<Student>();
        String sql="select * from student limit "+((pageNo-1)*pageCount)+","+pageCount;
        String sqlCount="select count(*) from student";

        try {
            ResultSet rs=dbCon.find(sql);
            while(rs.next()){
                int stuID = rs.getInt("stu_id");
                String stuNum = rs.getString("stu_num");
                String stuName = rs.getString("stu_name");
                String stuSex = rs.getString("stu_sex");
                int stuAge = rs.getInt("stu_age");
                String stuClass = rs.getString("stu_class");
                String major = rs.getString("major");
                String department = rs.getString("department");
                student = new Student(stuID, stuNum, stuName, stuSex, stuAge,
                        stuClass, major, department);
                list.add(student);
            }
            rs=dbCon.find(sqlCount);
            while(rs.next()){
                totalCount=rs.getInt(1);
            }
            pageBean=new PageBean(list ,  totalCount,  pageNo,  pageCount);
        } catch (SQLException e) {
            dbCon.close();
            e.printStackTrace();
        }
        return pageBean;
    }
}
